﻿

create proc [Products].[GetServices]
as
begin
	select	p.ProductID,
			sc.SubCategorie + ' (' + rtrim(sc.SubCategorieID) + ')' 'SubCategorie',
			ca.Categorie + ' (' + rtrim(ca.CategorieID) + ')' 'Categorie',
			p.ProductName,
			p.Unity,
			p.ItemNumber,
			p.ListPrice,
			p.Rabatt1,
			p.Rabatt2,
			p.Skonto,
			p.CostPrice,
			p.SurCharge,
			p.SalesPrice,
			p.ContributionMargin,
			coalesce(manufacturer.Suchbegriff, 'Di') 'Manufacturer',
			manufacturer.OrgKey 'ManufacturerOrgKey',
			coalesce(provider.Suchbegriff,'Di') 'Provider',
			provider.OrgKey 'ProviderOrgKey',
			p.TaxCode,
			p.Effort, --Leistung
			p.[Hour]  --Zeit in Stunden
	from	Products.datProducts p
			left join Products.domSubCategorie sc on
				sc.SubCategorieID = p.SubCategorieID
			inner join Products.domCategorie ca on
				ca.CategorieID = p.CategorieID
			left join (
				select	pt.ProductID,
						o.Suchbegriff,
						o.OrgKey,
						pt.ProductOrgTypeID,
						dpot.ProductOrgType
				from	Products.mnProductOrgTypes pt
						inner join Products.domProductOrgTypes dpot on
							dpot.ProductOrgTypeID = pt.ProductOrgTypeID
						inner join dbo.Organisation o on
							o.OrgKey = pt.OrgKey
				where	pt.ProductOrgTypeID = 1
				) manufacturer  on
					manufacturer.ProductID = p.ProductID
			left join (
				select	pt.ProductID,
						o.Suchbegriff,
						o.OrgKey,
						pt.ProductOrgTypeID,
						dpot.ProductOrgType
				from	Products.mnProductOrgTypes pt
						inner join Products.domProductOrgTypes dpot on
							dpot.ProductOrgTypeID = pt.ProductOrgTypeID
						inner join dbo.Organisation o on
							o.OrgKey = pt.OrgKey
				where	pt.ProductOrgTypeID = 2
				) provider  on
					provider.ProductID = p.ProductID
	where	p.ProductTypeGroupID = 'Le'
	order by p.ProductName
end


GO


